using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;

namespace ASPNETVN.PORTAL.Modules.NEWS.Components
{
    public partial class Item
    {
        #region Private members.

        protected int _ID;
        protected int _CategoryID;
        protected int _FlowID;
        protected string _Title = string.Empty;
        protected string _SubTitle = string.Empty;
        protected string _Memo = string.Empty;
        protected string _ImagePath = string.Empty;
        protected string _Authors = string.Empty;
        protected byte _Rate;
        protected string _Status = string.Empty;
        protected int _TotalViews;
        protected int _TotalComments;
        protected string _Keywords = string.Empty;
        protected string _CreatedBy = string.Empty;
        protected DateTime _CreatedDate;
        protected string _LastUpdatedBy = string.Empty;
        protected DateTime _LastUpdatedDate;
        protected string _ApprovedBy = string.Empty;
        protected DateTime _ApprovedDate;

        #endregion

        //---------------------------------------------------------------------------------------------

        #region Properties.

        public int ID
        {
            set { this._ID = value; }
            get { return this._ID; }
        }

        public int CategoryID
        {
            set { this._CategoryID = value; }
            get { return this._CategoryID; }
        }

        public int FlowID
        {
            set { this._FlowID = value; }
            get { return this._FlowID; }
        }

        public string Title
        {
            set { this._Title = value; }
            get { return this._Title; }
        }

        public string SubTitle
        {
            set { this._SubTitle = value; }
            get { return this._SubTitle; }
        }

        public string Memo
        {
            set { this._Memo = value; }
            get { return this._Memo; }
        }

        public string ImagePath
        {
            set { this._ImagePath = value; }
            get { return this._ImagePath; }
        }

        public string Authors
        {
            set { this._Authors = value; }
            get { return this._Authors; }
        }

        public byte Rate
        {
            set { this._Rate = value; }
            get { return this._Rate; }
        }

        public string Status
        {
            set { this._Status = value; }
            get { return this._Status; }
        }

        public int TotalViews
        {
            set { this._TotalViews = value; }
            get { return this._TotalViews; }
        }

        public int TotalComments
        {
            set { this._TotalComments = value; }
            get { return this._TotalComments; }
        }

        public string Keywords
        {
            set { this._Keywords = value; }
            get { return this._Keywords; }
        }

        public string CreatedBy
        {
            set { this._CreatedBy = value; }
            get { return this._CreatedBy; }
        }

        public DateTime CreatedDate
        {
            set { this._CreatedDate = value; }
            get { return this._CreatedDate; }
        }

        public string LastUpdatedBy
        {
            set { this._LastUpdatedBy = value; }
            get { return this._LastUpdatedBy; }
        }

        public DateTime LastUpdatedDate
        {
            set { this._LastUpdatedDate = value; }
            get { return this._LastUpdatedDate; }
        }

        public string ApprovedBy
        {
            set { this._ApprovedBy = value; }
            get { return this._ApprovedBy; }
        }

        public DateTime ApprovedDate
        {
            set { this._ApprovedDate = value; }
            get { return this._ApprovedDate; }
        }


        #endregion

        //---------------------------------------------------------------------------------------------

        #region Select methods.

        public static Item Load(int iD)
        {
            const string spName = "[dbo].[p_NEWS_Item_Load]";
            SqlDatabase db = (SqlDatabase)DatabaseFactory.CreateDatabase();
            SqlCommand dbCommand = (SqlCommand)db.GetStoredProcCommand(spName);

            db.AddInParameter(dbCommand, "@ID", SqlDbType.Int, iD);
            Item entity = null;
            IDataReader reader = db.ExecuteReader(dbCommand);
            if (reader.Read())
            {
                entity = new Item();
                if (!reader.IsDBNull(reader.GetOrdinal("ID"))) entity.ID = reader.GetInt32(reader.GetOrdinal("ID"));
                if (!reader.IsDBNull(reader.GetOrdinal("CategoryID"))) entity.CategoryID = reader.GetInt32(reader.GetOrdinal("CategoryID"));
                if (!reader.IsDBNull(reader.GetOrdinal("FlowID"))) entity.FlowID = reader.GetInt32(reader.GetOrdinal("FlowID"));
                if (!reader.IsDBNull(reader.GetOrdinal("Title"))) entity.Title = reader.GetString(reader.GetOrdinal("Title"));
                if (!reader.IsDBNull(reader.GetOrdinal("SubTitle"))) entity.SubTitle = reader.GetString(reader.GetOrdinal("SubTitle"));
                if (!reader.IsDBNull(reader.GetOrdinal("Memo"))) entity.Memo = reader.GetString(reader.GetOrdinal("Memo"));
                if (!reader.IsDBNull(reader.GetOrdinal("ImagePath"))) entity.ImagePath = reader.GetString(reader.GetOrdinal("ImagePath"));
                if (!reader.IsDBNull(reader.GetOrdinal("Authors"))) entity.Authors = reader.GetString(reader.GetOrdinal("Authors"));
                if (!reader.IsDBNull(reader.GetOrdinal("Rate"))) entity.Rate = reader.GetByte(reader.GetOrdinal("Rate"));
                if (!reader.IsDBNull(reader.GetOrdinal("Status"))) entity.Status = reader.GetString(reader.GetOrdinal("Status"));
                if (!reader.IsDBNull(reader.GetOrdinal("TotalViews"))) entity.TotalViews = reader.GetInt32(reader.GetOrdinal("TotalViews"));
                if (!reader.IsDBNull(reader.GetOrdinal("TotalComments"))) entity.TotalComments = reader.GetInt32(reader.GetOrdinal("TotalComments"));
                if (!reader.IsDBNull(reader.GetOrdinal("Keywords"))) entity.Keywords = reader.GetString(reader.GetOrdinal("Keywords"));
                if (!reader.IsDBNull(reader.GetOrdinal("CreatedBy"))) entity.CreatedBy = reader.GetString(reader.GetOrdinal("CreatedBy"));
                if (!reader.IsDBNull(reader.GetOrdinal("CreatedDate"))) entity.CreatedDate = reader.GetDateTime(reader.GetOrdinal("CreatedDate"));
                if (!reader.IsDBNull(reader.GetOrdinal("LastUpdatedBy"))) entity.LastUpdatedBy = reader.GetString(reader.GetOrdinal("LastUpdatedBy"));
                if (!reader.IsDBNull(reader.GetOrdinal("LastUpdatedDate"))) entity.LastUpdatedDate = reader.GetDateTime(reader.GetOrdinal("LastUpdatedDate"));
                if (!reader.IsDBNull(reader.GetOrdinal("ApprovedBy"))) entity.ApprovedBy = reader.GetString(reader.GetOrdinal("ApprovedBy"));
                if (!reader.IsDBNull(reader.GetOrdinal("ApprovedDate"))) entity.ApprovedDate = reader.GetDateTime(reader.GetOrdinal("ApprovedDate"));
            }
            reader.Close();
            dbCommand.Connection.Close();

            return entity;
        }

        //---------------------------------------------------------------------------------------------

        public static DataSet SelectBy_CategoryID(int categoryID)
        {
            const string spName = "[dbo].[p_NEWS_Item_SelectBy_CategoryID]";
            SqlDatabase db = (SqlDatabase)DatabaseFactory.CreateDatabase();
            SqlCommand dbCommand = (SqlCommand)db.GetStoredProcCommand(spName);

            db.AddInParameter(dbCommand, "@CategoryID", SqlDbType.Int, categoryID);

            return db.ExecuteDataSet(dbCommand);
        }
        //---------------------------------------------------------------------------------------------
        public static DataSet SelectBy_FlowID(int flowID)
        {
            const string spName = "[dbo].[p_NEWS_Item_SelectBy_FlowID]";
            SqlDatabase db = (SqlDatabase)DatabaseFactory.CreateDatabase();
            SqlCommand dbCommand = (SqlCommand)db.GetStoredProcCommand(spName);

            db.AddInParameter(dbCommand, "@FlowID", SqlDbType.Int, flowID);

            return db.ExecuteDataSet(dbCommand);
        }
        //---------------------------------------------------------------------------------------------

        public static DataSet SelectAll()
        {
            const string spName = "[dbo].[p_NEWS_Item_SelectAll]";
            SqlDatabase db = (SqlDatabase)DatabaseFactory.CreateDatabase();
            SqlCommand dbCommand = (SqlCommand)db.GetStoredProcCommand(spName);


            return db.ExecuteDataSet(dbCommand);
        }

        //---------------------------------------------------------------------------------------------

        public static IDataReader SelectReaderAll()
        {
            const string spName = "[dbo].[p_NEWS_Item_SelectAll]";
            SqlDatabase db = (SqlDatabase)DatabaseFactory.CreateDatabase();
            SqlCommand dbCommand = (SqlCommand)db.GetStoredProcCommand(spName);


            return db.ExecuteReader(dbCommand);
        }

        //---------------------------------------------------------------------------------------------

        public static DataSet SelectDynamic(string whereCondition, string orderByExpression)
        {
            const string spName = "[dbo].[p_NEWS_Item_SelectDynamic]";
            SqlDatabase db = (SqlDatabase)DatabaseFactory.CreateDatabase();
            SqlCommand dbCommand = (SqlCommand)db.GetStoredProcCommand(spName);

            db.AddInParameter(dbCommand, "@WhereCondition", SqlDbType.NVarChar, whereCondition);
            db.AddInParameter(dbCommand, "@OrderByExpression", SqlDbType.NVarChar, orderByExpression);

            return db.ExecuteDataSet(dbCommand);
        }

        //---------------------------------------------------------------------------------------------

        public static IDataReader SelectReaderDynamic(string whereCondition, string orderByExpression)
        {
            const string spName = "[dbo].[p_NEWS_Item_SelectDynamic]";
            SqlDatabase db = (SqlDatabase)DatabaseFactory.CreateDatabase();
            SqlCommand dbCommand = (SqlCommand)db.GetStoredProcCommand(spName);

            db.AddInParameter(dbCommand, "@WhereCondition", SqlDbType.NVarChar, whereCondition);
            db.AddInParameter(dbCommand, "@OrderByExpression", SqlDbType.NVarChar, orderByExpression);

            return db.ExecuteReader(dbCommand);
        }

        //---------------------------------------------------------------------------------------------

        #endregion

        //---------------------------------------------------------------------------------------------

        #region Insert methods.

        public static int Insert(int categoryID, int flowID, string title, string subTitle, string memo, string imagePath, string authors, byte rate, string status, int totalViews, int totalComments, string keywords, string createdBy, DateTime createdDate, string lastUpdatedBy, DateTime lastUpdatedDate, string approvedBy, DateTime approvedDate)
        {
            Item entity = new Item();
            entity.CategoryID = categoryID;
            entity.FlowID = flowID;
            entity.Title = title;
            entity.SubTitle = subTitle;
            entity.Memo = memo;
            entity.ImagePath = imagePath;
            entity.Authors = authors;
            entity.Rate = rate;
            entity.Status = status;
            entity.TotalViews = totalViews;
            entity.TotalComments = totalComments;
            entity.Keywords = keywords;
            entity.CreatedBy = createdBy;
            entity.CreatedDate = createdDate;
            entity.LastUpdatedBy = lastUpdatedBy;
            entity.LastUpdatedDate = lastUpdatedDate;
            entity.ApprovedBy = approvedBy;
            entity.ApprovedDate = approvedDate;
            return entity.Insert();
        }

        public int Insert()
        {
            return this.Insert(null);
        }

        //---------------------------------------------------------------------------------------------

        public int Insert(SqlTransaction transaction)
        {
            const string spName = "[dbo].[p_NEWS_Item_Insert]";
            SqlDatabase db = (SqlDatabase)DatabaseFactory.CreateDatabase();
            SqlCommand dbCommand = (SqlCommand)db.GetStoredProcCommand(spName);

            db.AddOutParameter(dbCommand, "@ID", SqlDbType.Int, 4);
            db.AddInParameter(dbCommand, "@CategoryID", SqlDbType.Int, this._CategoryID);
            db.AddInParameter(dbCommand, "@FlowID", SqlDbType.Int, this._FlowID);
            db.AddInParameter(dbCommand, "@Title", SqlDbType.NVarChar, this._Title);
            db.AddInParameter(dbCommand, "@SubTitle", SqlDbType.NVarChar, this._SubTitle);
            db.AddInParameter(dbCommand, "@Memo", SqlDbType.NText, this._Memo);
            db.AddInParameter(dbCommand, "@ImagePath", SqlDbType.NVarChar, this._ImagePath);
            db.AddInParameter(dbCommand, "@Authors", SqlDbType.NVarChar, this._Authors);
            db.AddInParameter(dbCommand, "@Rate", SqlDbType.TinyInt, this._Rate);
            db.AddInParameter(dbCommand, "@Status", SqlDbType.NVarChar, this._Status);
            db.AddInParameter(dbCommand, "@TotalViews", SqlDbType.Int, this._TotalViews);
            db.AddInParameter(dbCommand, "@TotalComments", SqlDbType.Int, this._TotalComments);
            db.AddInParameter(dbCommand, "@Keywords", SqlDbType.NVarChar, this._Keywords);
            db.AddInParameter(dbCommand, "@CreatedBy", SqlDbType.NVarChar, this._CreatedBy);
            db.AddInParameter(dbCommand, "@CreatedDate", SqlDbType.DateTime, this._CreatedDate);
            db.AddInParameter(dbCommand, "@LastUpdatedBy", SqlDbType.VarChar, this._LastUpdatedBy);
            db.AddInParameter(dbCommand, "@LastUpdatedDate", SqlDbType.DateTime, this._LastUpdatedDate);
            db.AddInParameter(dbCommand, "@ApprovedBy", SqlDbType.VarChar, this._ApprovedBy);
            db.AddInParameter(dbCommand, "@ApprovedDate", SqlDbType.DateTime, this._ApprovedDate);

            if (transaction != null)
            {
                db.ExecuteNonQuery(dbCommand, transaction);
                this._ID = (int)db.GetParameterValue(dbCommand, "@ID");
                return this._ID;
            }
            else
            {
                db.ExecuteNonQuery(dbCommand);
                this._ID = (int)db.GetParameterValue(dbCommand, "@ID");
                return this._ID;
            }
        }

        //---------------------------------------------------------------------------------------------

        #endregion

        //---------------------------------------------------------------------------------------------

        #region Update methods.

        public static int Update(int iD, int categoryID, int flowID, string title, string subTitle, string memo, string imagePath, string authors, byte rate, string status, int totalViews, int totalComments, string keywords, string createdBy, DateTime createdDate, string lastUpdatedBy, DateTime lastUpdatedDate, string approvedBy, DateTime approvedDate)
        {
            Item entity = new Item();
            entity.ID = iD;
            entity.CategoryID = categoryID;
            entity.FlowID = flowID;
            entity.Title = title;
            entity.SubTitle = subTitle;
            entity.Memo = memo;
            entity.ImagePath = imagePath;
            entity.Authors = authors;
            entity.Rate = rate;
            entity.Status = status;
            entity.TotalViews = totalViews;
            entity.TotalComments = totalComments;
            entity.Keywords = keywords;
            entity.CreatedBy = createdBy;
            entity.CreatedDate = createdDate;
            entity.LastUpdatedBy = lastUpdatedBy;
            entity.LastUpdatedDate = lastUpdatedDate;
            entity.ApprovedBy = approvedBy;
            entity.ApprovedDate = approvedDate;
            return entity.Update();
        }

        public int Update()
        {
            return this.Update(null);
        }

        //---------------------------------------------------------------------------------------------

        public int Update(SqlTransaction transaction)
        {
            const string spName = "[dbo].[p_NEWS_Item_Update]";
            SqlDatabase db = (SqlDatabase)DatabaseFactory.CreateDatabase();
            SqlCommand dbCommand = (SqlCommand)db.GetStoredProcCommand(spName);

            db.AddInParameter(dbCommand, "@ID", SqlDbType.Int, this._ID);
            db.AddInParameter(dbCommand, "@CategoryID", SqlDbType.Int, this._CategoryID);
            db.AddInParameter(dbCommand, "@FlowID", SqlDbType.Int, this._FlowID);
            db.AddInParameter(dbCommand, "@Title", SqlDbType.NVarChar, this._Title);
            db.AddInParameter(dbCommand, "@SubTitle", SqlDbType.NVarChar, this._SubTitle);
            db.AddInParameter(dbCommand, "@Memo", SqlDbType.NText, this._Memo);
            db.AddInParameter(dbCommand, "@ImagePath", SqlDbType.NVarChar, this._ImagePath);
            db.AddInParameter(dbCommand, "@Authors", SqlDbType.NVarChar, this._Authors);
            db.AddInParameter(dbCommand, "@Rate", SqlDbType.TinyInt, this._Rate);
            db.AddInParameter(dbCommand, "@Status", SqlDbType.NVarChar, this._Status);
            db.AddInParameter(dbCommand, "@TotalViews", SqlDbType.Int, this._TotalViews);
            db.AddInParameter(dbCommand, "@TotalComments", SqlDbType.Int, this._TotalComments);
            db.AddInParameter(dbCommand, "@Keywords", SqlDbType.NVarChar, this._Keywords);
            db.AddInParameter(dbCommand, "@CreatedBy", SqlDbType.NVarChar, this._CreatedBy);
            db.AddInParameter(dbCommand, "@CreatedDate", SqlDbType.DateTime, this._CreatedDate);
            db.AddInParameter(dbCommand, "@LastUpdatedBy", SqlDbType.VarChar, this._LastUpdatedBy);
            db.AddInParameter(dbCommand, "@LastUpdatedDate", SqlDbType.DateTime, this._LastUpdatedDate);
            db.AddInParameter(dbCommand, "@ApprovedBy", SqlDbType.VarChar, this._ApprovedBy);
            db.AddInParameter(dbCommand, "@ApprovedDate", SqlDbType.DateTime, this._ApprovedDate);

            if (transaction != null)
                return db.ExecuteNonQuery(dbCommand, transaction);
            else
                return db.ExecuteNonQuery(dbCommand);
        }

        //---------------------------------------------------------------------------------------------

        #endregion

        //---------------------------------------------------------------------------------------------

        #region Delete methods.

        public static int Delete(int iD)
        {
            Item entity = new Item();
            entity.ID = iD;
            return entity.Delete();
        }

        public int Delete()
        {
            return this.Delete(null);
        }

        //---------------------------------------------------------------------------------------------

        public int Delete(SqlTransaction transaction)
        {
            const string spName = "[dbo].[p_NEWS_Item_Delete]";
            SqlDatabase db = (SqlDatabase)DatabaseFactory.CreateDatabase();
            SqlCommand dbCommand = (SqlCommand)db.GetStoredProcCommand(spName);

            db.AddInParameter(dbCommand, "@ID", SqlDbType.Int, this._ID);

            if (transaction != null)
                return db.ExecuteNonQuery(dbCommand, transaction);
            else
                return db.ExecuteNonQuery(dbCommand);
        }

        //---------------------------------------------------------------------------------------------

        public static int DeleteBy_CategoryID(int categoryID)
        {
            string spName = "[dbo].[p_NEWS_Item_DeleteBy_CategoryID]";
            SqlDatabase db = (SqlDatabase)DatabaseFactory.CreateDatabase();
            SqlCommand dbCommand = (SqlCommand)db.GetStoredProcCommand(spName);

            db.AddInParameter(dbCommand, "@CategoryID", SqlDbType.Int, categoryID);

            return db.ExecuteNonQuery(dbCommand);
        }

        //---------------------------------------------------------------------------------------------

        public static int DeleteBy_FlowID(int flowID)
        {
            string spName = "[dbo].[p_NEWS_Item_DeleteBy_FlowID]";
            SqlDatabase db = (SqlDatabase)DatabaseFactory.CreateDatabase();
            SqlCommand dbCommand = (SqlCommand)db.GetStoredProcCommand(spName);

            db.AddInParameter(dbCommand, "@FlowID", SqlDbType.Int, flowID);

            return db.ExecuteNonQuery(dbCommand);
        }

        //---------------------------------------------------------------------------------------------

        #endregion
    }
}